﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NamingOnLessonUtility;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Data.SqlClient;
namespace StandardOnline.DataAccess
{
    /// <summary>
    /// SqlServer数据库操作基类
    /// </summary>
    public class SqlHelper
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        public static string ConnectionString { get; set; }

        public SqlHelper(string connstring)
        {
            string ConStringDESEncrypt = ConfigHelper.AppSettings("ConStringDESEncrypt");
            ConnectionString = ConfigurationManager.ConnectionStrings[connstring].ConnectionString;
        }

        /// <summary>
        /// 执行 SQL 语句，并返回受影响的行数。
        /// </summary>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">存储过程名称或者T-SQL命令行</param>
        /// <param name="parameters">执行命令所需的sql语句对应参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] parameters)
        {
            int num = 0;
            try
            {
                SqlCommand cmd = new SqlCommand();
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, parameters);
                    num = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
            }
            catch (Exception ex)
            {
                num = -1;
            }
            return num;
        }
        /// <summary>
        /// 执行 SQL 语句，并返回受影响的行数。
        /// </summary>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">存储过程名称或者T-SQL命令行</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(CommandType cmdType, string cmdText)
        {
            int num = 0;
            try
            {
                SqlCommand cmd = new SqlCommand();
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, null);
                    num = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }

            }

            catch (Exception ex)

            {

                num = -1;

                

            }

            return num;

        }

        /// <summary>

        /// 执行 SQL 语句，并返回受影响的行数。

        /// </summary>

        /// <param name="conn">数据库连接对象</param>

        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>

        /// <param name="cmdText">存储过程名称或者T-SQL命令行</param>

        /// <param name="parameters">执行命令所需的sql语句对应参数</param>

        /// <returns></returns>

        public static int ExecuteNonQuery(DbConnection connection, CommandType cmdType, string cmdText, params DbParameter[] parameters)

        {

            int num = 0;

            try

            {

                SqlCommand cmd = new SqlCommand();

                PrepareCommand(cmd, connection, null, cmdType, cmdText, parameters);

                num = cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();

            }

            catch (Exception ex)

            {

                num = -1;

                

            }

            return num;

        }

        /// <summary>

        /// 执行 SQL 语句，并返回受影响的行数。

        /// </summary>

        /// <param name="conn">数据库连接对象</param>

        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>

        /// <param name="cmdText">存储过程名称或者T-SQL命令行</param>

        /// <returns></returns>

        public static int ExecuteNonQuery(DbConnection connection, CommandType cmdType, string cmdText)

        {

            int num = 0;

            try

            {

                SqlCommand cmd = new SqlCommand();

                PrepareCommand(cmd, connection, null, cmdType, cmdText, null);

                num = cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();

            }

            catch (Exception ex)

            {

                num = -1;

                

            }

            return num;

        }

        /// <summary>

        /// 执行 SQL 语句，并返回受影响的行数。

        /// </summary>

        /// <param name="isOpenTrans">事务对象</param>

        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>

        /// <param name="cmdText">存储过程名称或者T-SQL命令行</param>

        /// <param name="parameters">执行命令所需的sql语句对应参数</param>

        /// <returns></returns>

        public static int ExecuteNonQuery(DbTransaction isOpenTrans, CommandType cmdType, string cmdText, params DbParameter[] parameters)

        {

            int num = 0;

            try

            {

                SqlCommand cmd = new SqlCommand();

                if (isOpenTrans == null || isOpenTrans.Connection == null)

                {

                    using (SqlConnection conn = new SqlConnection(ConnectionString))

                    {

                        PrepareCommand(cmd, conn, isOpenTrans, cmdType, cmdText, parameters);

                        num = cmd.ExecuteNonQuery();

                    }

                }

                else

                {

                    PrepareCommand(cmd, isOpenTrans.Connection, isOpenTrans, cmdType, cmdText, parameters);

                    num = cmd.ExecuteNonQuery();

                }

                cmd.Parameters.Clear();

            }

            catch (Exception ex)

            {

                num = -1;

                

            }

            return num;

        }

        /// <summary>

        /// 执行 SQL 语句，并返回受影响的行数。

        /// </summary>

        /// <param name="isOpenTrans">事务对象</param>

        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>

        /// <param name="cmdText">存储过程名称或者T-SQL命令行</param>

        /// <returns></returns>

        public static int ExecuteNonQuery(DbTransaction isOpenTrans, CommandType cmdType, string cmdText)

        {

            int num = 0;

            try

            {

                SqlCommand cmd = new SqlCommand();

                PrepareCommand(cmd, isOpenTrans.Connection, isOpenTrans, cmdType, cmdText, null);

                num = cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();

            }

            catch (Exception ex)

            {

                num = -1;

                

            }

            return num;

        }

        /// <summary>

        /// 使用提供的参数，执行有结果集返回的数据库操作命令、并返回SqlDataReader对象

        /// </summary>

        /// <param name="isOpenTrans">事务对象</param>

        /// <param name="commandType">执行命令的类型（存储过程或T-SQL，等等）</param>

        /// <param name="commandText">存储过程名称或者T-SQL命令行<</param>

        /// <param name="parameters">执行命令所需的sql语句对应参数</param>

        /// <returns>返回SqlDataReader对象</returns>

        public static IDataReader ExecuteReader(DbTransaction isOpenTrans, CommandType cmdType, string cmdText, params DbParameter[] parameters)

        {

            SqlCommand cmd = new SqlCommand();

            SqlConnection conn = new SqlConnection(ConnectionString);

            try

            {

                PrepareCommand(cmd, conn, isOpenTrans, cmdType, cmdText, parameters);

                IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                cmd.Parameters.Clear();

                return rdr;

            }

            catch (Exception ex)

            {

                conn.Close();

                cmd.Dispose();

                

                throw;

            }

        }

        /// <summary>

        /// 使用提供的参数，执行有结果集返回的数据库操作命令、并返回SqlDataReader对象

        /// </summary>

        /// <param name="commandType">执行命令的类型（存储过程或T-SQL，等等）</param>

        /// <param name="commandText">存储过程名称或者T-SQL命令行<</param>

        /// <param name="parameters">执行命令所需的sql语句对应参数</param>

        /// <returns>返回SqlDataReader对象</returns>

        public static IDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] parameters)

        {

            SqlCommand cmd = new SqlCommand();

            SqlConnection conn = new SqlConnection(ConnectionString);

            try

            {

                PrepareCommand(cmd, conn, null, cmdType, cmdText, parameters);

                IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                cmd.Parameters.Clear();

                return rdr;

            }

            catch (Exception ex)

            {

                conn.Close();

                cmd.Dispose();

                

                throw;

            }

        }

        /// <summary>

        ///使用提供的参数，执行有结果集返回的数据库操作命令、并返回SqlDataReader对象

        /// </summary>

        /// <param name="commandType">执行命令的类型（存储过程或T-SQL，等等）</param>

        /// <param name="commandText">存储过程名称或者T-SQL命令行<</param>

        /// <returns>返回SqlDataReader对象</returns>

        public static IDataReader ExecuteReader(CommandType cmdType, string cmdText)

        {

            SqlCommand cmd = new SqlCommand();

            SqlConnection conn = new SqlConnection(ConnectionString);

            try

            {

                PrepareCommand(cmd, conn, null, cmdType, cmdText, null);

                IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                cmd.Parameters.Clear();

                return rdr;

            }

            catch (Exception ex)

            {

                conn.Close();

                cmd.Dispose();

                

                throw;

            }

        }

        /// <summary>

        /// 查询数据填充到数据集DataSet中

        /// </summary>

        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>

        /// <param name="cmdText">命令文本</param>

        /// <param name="parameters">sql语句对应参数</param>

        /// <returns>数据集DataSet对象</returns>

        public static DataSet GetDataSet(CommandType cmdType, string cmdText, params DbParameter[] parameters)

        {

            DataSet ds = new DataSet();

            SqlCommand cmd = new SqlCommand();

            SqlConnection conn = new SqlConnection(ConnectionString);

            try

            {

                PrepareCommand(cmd, conn, null, cmdType, cmdText, parameters);

                IDbDataAdapter sda = new SqlDataAdapter(cmd);

                sda.Fill(ds);

                return ds;

            }

            catch (Exception ex)

            {

                conn.Close();

                cmd.Dispose();

                

                throw;

            }

        }

        /// <summary>

        /// 查询数据填充到数据集DataSet中

        /// </summary>

        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>

        /// <param name="cmdText">命令文本</param>

        /// <returns>数据集DataSet对象</returns>

        public static DataSet GetDataSet(CommandType cmdType, string cmdText)

        {

            DataSet ds = new DataSet();

            SqlCommand cmd = new SqlCommand();

            SqlConnection conn = new SqlConnection(ConnectionString);

            try

            {

                PrepareCommand(cmd, conn, null, cmdType, cmdText, null);

                IDbDataAdapter sda = new SqlDataAdapter(cmd);

                sda.Fill(ds);

                return ds;

            }

            catch (Exception ex)

            {

                conn.Close();

                cmd.Dispose();

                

                throw;

            }

        }

        /// <summary>

        /// 依靠数据库连接字符串connectionString,

        /// 使用所提供参数，执行返回首行首列命令

        /// </summary>

        /// <param name="commandType">执行命令的类型（存储过程或T-SQL，等等）</param>

        /// <param name="commandText">存储过程名称或者T-SQL命令行</param>

        /// <param name="parameters">执行命令所需的sql语句对应参数</param>

        /// <returns>返回一个对象，使用Convert.To{Type}将该对象转换成想要的数据类型。</returns>

        public static object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] parameters)

        {

            try

            {

                SqlCommand cmd = new SqlCommand();

                using (DbConnection connection = new SqlConnection(ConnectionString))

                {

                    PrepareCommand(cmd, connection, null, cmdType, cmdText, parameters);

                    object val = cmd.ExecuteScalar();

                    cmd.Parameters.Clear();

                    return val;

                }

            }

            catch (Exception ex)

            {

                

                throw;

            }

        }

        /// <summary>

        /// 依靠数据库连接字符串connectionString,

        /// 使用所提供参数，执行返回首行首列命令

        /// </summary>

        /// <param name="commandType">执行命令的类型（存储过程或T-SQL，等等）</param>

        /// <param name="commandText">存储过程名称或者T-SQL命令行</param>

        /// <returns>返回一个对象，使用Convert.To{Type}将该对象转换成想要的数据类型。</returns>

        public static object ExecuteScalar(CommandType cmdType, string cmdText)

        {

            try

            {

                SqlCommand cmd = new SqlCommand();

                using (DbConnection connection = new SqlConnection(ConnectionString))

                {

                    PrepareCommand(cmd, connection, null, cmdType, cmdText, null);

                    object val = cmd.ExecuteScalar();

                    cmd.Parameters.Clear();

                    return val;

                }

            }

            catch (Exception ex)

            {

                

                throw;

            }

        }

        /// <summary>

        ///依靠数据库连接字符串connectionString,

        /// 使用所提供参数，执行返回首行首列命令

        /// </summary>

        /// <param name="connection">数据库连接对象</param>

        /// <param name="commandType">执行命令的类型（存储过程或T-SQL，等等）</param>

        /// <param name="commandText">存储过程名称或者T-SQL命令行</param>

        /// <param name="parameters">执行命令所需的sql语句对应参数</param>

        /// <returns>返回一个对象，使用Convert.To{Type}将该对象转换成想要的数据类型。</returns>

        public static object ExecuteScalar(DbConnection connection, CommandType cmdType, string cmdText, params DbParameter[] parameters)

        {

            try

            {

                SqlCommand cmd = new SqlCommand();

                PrepareCommand(cmd, connection, null, cmdType, cmdText, parameters);

                object val = cmd.ExecuteScalar();

                cmd.Parameters.Clear();

                return val;

            }

            catch (Exception ex)

            {

                

                throw;

            }

        }

        /// <summary>

        ///依靠数据库连接字符串connectionString,

        /// 使用所提供参数，执行返回首行首列命令

        /// </summary>

        /// <param name="connection">数据库连接对象</param>

        /// <param name="commandType">执行命令的类型（存储过程或T-SQL，等等）</param>

        /// <param name="commandText">存储过程名称或者T-SQL命令行</param>

        /// <param name="parameters">执行命令所需的sql语句对应参数</param>

        /// <returns>返回一个对象，使用Convert.To{Type}将该对象转换成想要的数据类型。</returns>

        public static object ExecuteScalar(DbConnection connection, CommandType cmdType, string cmdText)

        {

            try

            {

                SqlCommand cmd = new SqlCommand();

                PrepareCommand(cmd, connection, null, cmdType, cmdText, null);

                object val = cmd.ExecuteScalar();

                cmd.Parameters.Clear();

                return val;

            }

            catch (Exception ex)

            {

                

                throw;

            }

        }

        /// <summary>

        ///依靠数据库连接字符串connectionString,

        /// 使用所提供参数，执行返回首行首列命令

        /// </summary>

        /// <param name="connection">数据库连接对象</param>

        /// <param name="commandType">执行命令的类型（存储过程或T-SQL，等等）</param>

        /// <param name="commandText">存储过程名称或者T-SQL命令行</param>

        /// <param name="parameters">执行命令所需的sql语句对应参数</param>

        /// <returns>返回一个对象，使用Convert.To{Type}将该对象转换成想要的数据类型。</returns>

        public static object ExecuteScalar(DbConnection conn, DbTransaction isOpenTrans, CommandType cmdType, string cmdText)

        {

            try

            {

                SqlCommand cmd = new SqlCommand();

                PrepareCommand(cmd, conn, isOpenTrans, cmdType, cmdText, null);

                object val = cmd.ExecuteScalar();

                cmd.Parameters.Clear();

                return val;

            }

            catch (Exception ex)

            {

                

                throw;

            }

        }

        /// <summary>

        ///依靠数据库连接字符串connectionString,

        /// 使用所提供参数，执行返回首行首列命令

        /// </summary>

        /// <param name="isOpenTrans">事务</param>

        /// <param name="commandType">执行命令的类型（存储过程或T-SQL，等等）</param>

        /// <param name="commandText">存储过程名称或者T-SQL命令行</param>

        /// <param name="parameters">执行命令所需的sql语句对应参数</param>

        /// <returns>返回一个对象，使用Convert.To{Type}将该对象转换成想要的数据类型。</returns>

        public static object ExecuteScalar(DbTransaction isOpenTrans, CommandType cmdType, string cmdText, params DbParameter[] parameters)

        {

            try

            {

                SqlCommand cmd = new SqlCommand();

                PrepareCommand(cmd, isOpenTrans.Connection, isOpenTrans, cmdType, cmdText, parameters);

                object val = cmd.ExecuteScalar();

                cmd.Parameters.Clear();

                return val;

            }

            catch (Exception ex)

            {

                

                throw;

            }

        }

        /// <summary>

        /// 为即将执行准备一个命令

        /// </summary>

        /// <param name="cmd">SqlCommand对象</param>

        /// <param name="conn">SqlConnection对象</param>

        /// <param name="isOpenTrans">DbTransaction对象</param>

        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>

        /// <param name="cmdText">存储过程名称或者T-SQL命令行, e.g. Select * from Products</param>

        /// <param name="cmdParms">SqlParameters to use in the command</param>

        private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction isOpenTrans, CommandType cmdType, string cmdText, DbParameter[] cmdParms)

        {

            if (conn.State != ConnectionState.Open)

                conn.Open();

            cmd.Connection = conn;

            cmd.CommandText = cmdText;

            if (isOpenTrans != null)

                cmd.Transaction = isOpenTrans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)

            {

                cmd.Parameters.AddRange(cmdParms);

            }

        }



        #region 数据分页

        /// <summary>

        /// 摘要:

        ///     数据分页

        /// 参数：

        ///     sql：传入要执行sql语句

        ///     param：参数化

        ///     orderField：排序字段

        ///     orderType：排序类型

        ///     pageIndex：当前页

        ///     pageSize：页大小

        ///     count：返回查询条数

        /// </summary>

        public static DataTable GetPageTable(string sql, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)

        {

            StringBuilder strSql = new StringBuilder();

            if (pageIndex == 0)

            {

                pageIndex = 1;

            }

            int num = (pageIndex - 1) * pageSize;

            int num1 = (pageIndex) * pageSize;

            string OrderBy = "";

            if (!string.IsNullOrEmpty(orderField))

                OrderBy = "Order By " + orderField + " " + orderType + "";

            else

                OrderBy = "order by (select 0)";

            strSql.Append("Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");

            strSql.Append(" As rowNum, * From (" + sql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");

            count = Convert.ToInt32(ExecuteScalar(CommandType.Text, "Select Count(1) From (" + sql + ") As t", param));

            IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);

            return DatabaseReader.ReaderToDataTable(dr);

        }

        /// <summary>

        /// 摘要:

        ///     数据分页

        /// 参数：

        ///     sql：传入要执行sql语句

        ///     orderField：排序字段

        ///     orderType：排序类型

        ///     pageIndex：当前页

        ///     pageSize：页大小

        ///     count：返回查询条数

        /// </summary>

        public static DataTable GetPageTable(string sql, string orderField, string orderType, int pageIndex, int pageSize, ref int count)

        {

            return GetPageTable(sql, null, orderField, orderType, pageIndex, pageSize, ref count);

        }

        /// <summary>

        /// 摘要:

        ///     数据分页

        /// 参数：

        ///     sql：传入要执行sql语句

        ///     param：参数化

        ///     orderField：排序字段

        ///     orderType：排序类型

        ///     pageIndex：当前页

        ///     pageSize：页大小

        ///     count：返回查询条数

        /// </summary>

        public static List<T> GetPageList<T>(string sql, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)

        {

            StringBuilder strSql = new StringBuilder();

            if (pageIndex == 0)

            {

                pageIndex = 1;

            }

            int num = (pageIndex - 1) * pageSize;

            int num1 = (pageIndex) * pageSize;

            string OrderBy = "";

            if (!string.IsNullOrEmpty(orderField))

                OrderBy = "Order By " + orderField + " " + orderType + "";

            else

                OrderBy = "Order By (select 0)";

            strSql.Append("Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");

            strSql.Append(" As rowNum, * From (" + sql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");

            count = Convert.ToInt32(ExecuteScalar(CommandType.Text, "Select Count(1) From (" + sql + ") As t", param));

            IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);

            return DatabaseReader.ReaderToList<T>(dr);

        }

        /// <summary>

        /// 摘要:

        ///     数据分页

        /// 参数：

        ///     sql：传入要执行sql语句

        ///     orderField：排序字段

        ///     orderType：排序类型

        ///     pageIndex：当前页

        ///     pageSize：页大小

        ///     count：返回查询条数

        /// </summary>

        public static List<T> GetPageList<T>(string sql, string orderField, string orderType, int pageIndex, int pageSize, ref int count)

        {

            return GetPageList<T>(sql, null, orderField, orderType, pageIndex, pageSize, ref count);

        }

        #endregion



        /// <summary>

        /// 

        /// </summary>

        /// <param name="connectionString">目标连接字符</param>

        /// <param name="TableName">目标表</param>

        /// <param name="dt">源数据</param>

        public static object SqlBulkCopyByDatatable(string TableName, DataTable dt)

        {

            using (SqlConnection conn = new SqlConnection(ConnectionString))

            {

                using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.UseInternalTransaction))

                {

                    try

                    {

                        sqlbulkcopy.DestinationTableName = TableName;

                        for (int i = 0; i < dt.Columns.Count; i++)

                        {

                            sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);

                        }

                        sqlbulkcopy.WriteToServer(dt);

                    }

                    catch (System.Exception ex)

                    {

                        throw ex;

                    }

                    return 1;

                }

            }

        }

    }

}
